[CHAPTER 06. Replication과 Wait Event]

3. Wait Event
3-2. Client: WalSenderWaitForWal, Client: WalSenderWriteData


SELECT   *
FROM     pg_stat_replication;


4. CASE STUDY
4-1. CASE1: IPC:SyncRep


DROP TABLE IF EXISTS sync_test;
CREATE TABLE sync_test (
    a TEXT
);

/* Primary Server */
BEGIN;

INSERT INTO sync_test (a)
SELECT md5(random()::TEXT)
FROM generate_series(1, 100000);
INSERT INTO test_commit(data) VALUES (md5(random()::text));

COMMIT;

/* Primary Server */
SELECT   event_type,
         event,
         SUM(count) AS total_count
FROM     pg_wait_sampling_profile
WHERE    event IS NOT NULL
GROUP BY event_type, event
ORDER BY event_type ASC, total_count DESC;


4-2. CASE2: Client: WalSenderWaitForWal, Client: WalSenderWriteData


/* Publisher & Subscriber */
DROP TABLE IF EXISTS repl_test;
CREATE TABLE repl_test (
    a TEXT
);

/* Publisher */
BEGIN;

INSERT INTO repl_test(a)
VALUES (md5(random()::text));

COMMIT;

/* Publisher */
SELECT   event_type,
         event,
         SUM(count) AS total_count
FROM     pg_wait_sampling_profile
WHERE    event IS NOT NULL
GROUP BY event_type, event
ORDER BY event_type ASC, total_count DESC;

SELECT   slot_name,
         active,
         restart_lsn,
         pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS slot_lag,
         current_time
FROM     pg_replication_slots
WHERE    slot_name = 'test_slot';

/* Subscriber */
SELECT * 
FROM pg_stat_subsciption;


4-3. CASE3: IPC:RecoveryConflictSnapshot


/* Primary Server */
DROP TABLE IF EXISTS long_test;
CREATE TABLE long_test (
    a SERIAL PRIMARY KEY,
    b TEXT
);

INSERT INTO long_test (b)
SELECT   repeat(md5(random()::TEXT), 10)
FROM     generate_series(1, 50000);

BEGIN;

UPDATE   long_test
SET      b = md5(random()::TEXT)
WHERE    a = (random() * 50000)::INT;

COMMIT;

/* Standby Server */
SELECT   COUNT(*)
FROM     long_test t1
JOIN     long_test t2
         ON t1.b LIKE '%' || substring(t2.b FROM 1 FOR 5) || '%';
         
SELECT   event_type,
         event,
         SUM(count) AS total_count
FROM     pg_wait_sampling_profile
WHERE    event IS NOT NULL
GROUP BY event_type, event
ORDER BY event_type ASC, total_count DESC;         

SELECT   *
FROM     pg_stat_database_conflicts
WHERE    datname = 'postgres'; 

/* Primary Server */
SELECT   *
FROM     pg_stat_replication;

SELECT   relname,
         n_dead_tup,
         vacuum_count,
         last_autovacuum
FROM     pg_stat_all_tables
WHERE    relname = 'long_test';
